﻿Imports MySql.Data.MySqlClient

Public Class checkin
    Dim roomType As Integer = 0
    Dim connString As String = "Server=localhost; User Id=root; Password=; Database=leximoore"

    Public Sub insert()
        Dim adults As Integer = checkInForm.adultsComboBox.SelectedIndex + 1
        Dim children As Integer = checkInForm.childrenComboBox.SelectedIndex
        Dim checkIn As String = checkInForm.checkInDatePicker.Value.ToString()
        Dim checkOut As String = checkInForm.checkOutDatePicker.Value.ToString()
        Dim firstName As String = userDetailsForm.firstNameTxt.Text
        Dim lastName As String = userDetailsForm.lastNameTxt.Text
        Dim contact As Long = userDetailsForm.contactTxt.Text
        Dim email As String = userDetailsForm.emailTxt.Text
        Dim creditCard As Long = userDetailsForm.creditCardTxt.Text
        Dim roomNo As String = userDetailsForm.roomNoTxt.Text
        If (userDetailsForm.roomTypeTxt.Text = "President Suite") Then
            roomType = 1
        Else
            roomType = 2
        End If
        Randomize()
        Dim code As Integer = CInt(Int((9999 * Rnd()) + 1000))

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "INSERT INTO reservation (adults, child, arrival, departure, firstname, lastname, contact, email, credit_card, no_room, room_id, confirmation) values (@adults,@children,@checkIn,@checkOut,@firstName,@lastName,@contact,@email,@creditCard,@noRoom,@roomType,@code)"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@adults", adults)
                    .Parameters.AddWithValue("@children", children)
                    .Parameters.AddWithValue("@checkIn", checkIn)
                    .Parameters.AddWithValue("@checkOut", checkOut)
                    .Parameters.AddWithValue("@firstName", firstName)
                    .Parameters.AddWithValue("@lastName", lastName)
                    .Parameters.AddWithValue("@contact", contact)
                    .Parameters.AddWithValue("@email", email)
                    .Parameters.AddWithValue("@creditCard", creditCard)
                    .Parameters.AddWithValue("@noRoom", 1)
                    .Parameters.AddWithValue("@roomType", roomType)
                    .Parameters.AddWithValue("@code", code)
                    .Parameters.AddWithValue("@roomNo", roomNo)
                    .Parameters.AddWithValue("@type", userDetailsForm.roomTypeTxt.Text)

                End With

                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()

                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()

                Finally
                    SQLConnection.Close()

                End Try
            End Using
        End Using

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "INSERT INTO roominventory (room_no, arrival, departure, room_id, confirmation) values (@roomNo,@checkIn,@checkOut,@roomType,@code)"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@adults", adults)
                    .Parameters.AddWithValue("@children", children)
                    .Parameters.AddWithValue("@checkIn", checkIn)
                    .Parameters.AddWithValue("@checkOut", checkOut)
                    .Parameters.AddWithValue("@firstName", firstName)
                    .Parameters.AddWithValue("@lastName", lastName)
                    .Parameters.AddWithValue("@contact", contact)
                    .Parameters.AddWithValue("@email", email)
                    .Parameters.AddWithValue("@creditCard", creditCard)
                    .Parameters.AddWithValue("@noRoom", 1)
                    .Parameters.AddWithValue("@roomType", roomType)
                    .Parameters.AddWithValue("@code", code)
                    .Parameters.AddWithValue("@roomNo", roomNo)
                    .Parameters.AddWithValue("@type", userDetailsForm.roomTypeTxt.Text)

                End With

                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()

                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()

                Finally
                    SQLConnection.Close()

                End Try
            End Using
        End Using

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "INSERT INTO room (room_id, type) values (@roomType, @type)"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@adults", adults)
                    .Parameters.AddWithValue("@children", children)
                    .Parameters.AddWithValue("@checkIn", checkIn)
                    .Parameters.AddWithValue("@checkOut", checkOut)
                    .Parameters.AddWithValue("@firstName", firstName)
                    .Parameters.AddWithValue("@lastName", lastName)
                    .Parameters.AddWithValue("@contact", contact)
                    .Parameters.AddWithValue("@email", email)
                    .Parameters.AddWithValue("@creditCard", creditCard)
                    .Parameters.AddWithValue("@noRoom", 1)
                    .Parameters.AddWithValue("@roomType", roomType)
                    .Parameters.AddWithValue("@code", code)
                    .Parameters.AddWithValue("@roomNo", roomNo)
                    .Parameters.AddWithValue("@type", userDetailsForm.roomTypeTxt.Text)

                End With

                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()

                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()

                Finally
                    SQLConnection.Close()

                End Try
            End Using
        End Using

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "UPDATE room SET qty = qty - 1 WHERE room_id=@roomType"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@roomType", roomType)

                End With

                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()
                    MessageBox.Show(" Your booking code is: " + code.ToString + ". Please note it down as you will need it for check-out!")

                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()

                Finally
                    SQLConnection.Close()

                End Try
            End Using
        End Using
    End Sub
End Class
